var common = require('../../common');
var pool = require('../index');
var mysql = require('mysql');
var YjxBaseModel = require('./YjxBaseModel');
var CONFIG = require('../../config');


var YjxActivityModel = function (obj) {
    YjxBaseModel.call(this,obj);

    // `act_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '活动id',
    // `act_no` varchar(50) NOT NULL DEFAULT '' COMMENT '活动编号（对外展示用）',
    // `type` int(3) NOT NULL DEFAULT '10' COMMENT '活动分类: 10:推广有奖活动',
    // `org_id` bigint(20) NOT NULL COMMENT '活动组织id',
    // `as_id` bigint(20) NOT NULL COMMENT '活动配置id',
    // `state` int(3) NOT NULL DEFAULT '10' COMMENT '活动状态: 10未开场（当前时间<开始时间）[预留]、20已开场（结束时间>当前时间>=开始时间）[预留]、30活动截止（当前时间>=结束时间）[预留]、40活动关闭[use]、50活动取消[use]',
    // `title` text NOT NULL COMMENT '活动标题',
    // `start_time` datetime NOT NULL COMMENT '有效期开始日时',
    // `end_time` datetime NOT NULL COMMENT '有效期结束日时',
    // `desc` text COMMENT '活动备注',
    // `theme_img` text COMMENT '活动主图',
    // `images` text COMMENT '活动详细图,JSON数组',
    // `url` text COMMENT '链接（预留）',
    // `order` int(3) NOT NULL DEFAULT '999' COMMENT '排序',
    // `publish` int(1) NOT NULL DEFAULT '0' COMMENT '发布状态: 0未发布, 1发布',
    // `create_time` datetime NOT NULL,
    // `update_time` datetime NOT NULL,
    // `update_by` varchar(50) DEFAULT NULL,
    // `del_flg` int(1) NOT NULL DEFAULT '0',

};

YjxActivityModel.prototype = new YjxBaseModel();

// 取得表名
YjxActivityModel.prototype.getTableName = function(){
    return 'yjx_activity';
};
// 取得查询语句中where 后面匹配主键的条件（需要各派生类进行重载）
YjxActivityModel.prototype.getWhereConditionOfKeyEqual = function(){
    return 'act_id='+this.act_id;
};


/**
 * 获取单个活动的配置信息
 *
 * @author 王俊
 * @date 2018-06-08
 *
 * @param activityId 活动 id
 * @param callback  回调
 *          error: 查询失败
 *          data : yjx_activity + yjx_act_setting 行数据(部分字段重命名)
 */
YjxActivityModel.prototype.getSingleActivitySettingInfo = function (activityId, callback) {

    pool.pool.getConnection(function(getConErr, connection) {
        if (getConErr) {
            console.log('YjxActivityModel.getSingleActivitySettingInfo Err:'+getConErr);
            return callback(getConErr,null);
        }

        var sql =
            ' SELECT ya.act_id, ' +
            '        CASE ' +
            '            WHEN ya.state > 30 THEN ya.state ' +
            '            WHEN ya.start_time > SYSDATE() THEN 10 ' +
            '            WHEN ya.end_time <= SYSDATE() THEN 30 ' +
            '            ELSE 20 ' +
            '        END AS state, ' +
            '        ya.theme_img, ' +
            '        ya.url, ' +
            '        ya.title, ' +
            '        ya.desc, ' +
            '        DATE_FORMAT(ya.start_time, "%Y.%m.%d") AS startDate, ' +
            '        DATE_FORMAT(ya.end_time, "%Y.%m.%d") AS endDate, ' +
            '        UNIX_TIMESTAMP(ya.start_time) AS startTimestamp, ' +
            '        UNIX_TIMESTAMP(ya.end_time) AS endTimestamp, ' +
            '        ya.type, ' +
            '        ya.publish, ' +
            '        yas.platform_type, ' +
            '        ya.images, ' +
            '        ya.share_img, ' +
            '        yo.name AS org_name, ' +
            '        yas.as_id, ' +
            '        yas.reward_total, ' +
            '        yas.sub_reward_1, ' +
            '        yas.sub_reward_2, ' +
            '        yas.product_limit, ' +
            '        yas.field1, ' +
            '        yas.field2, ' +
            '        yas.field3, ' +
            '        yas.field4, ' +
            '        yas.field5, ' +
            '        yas.weight1, ' +
            '        yas.weight2, ' +
            '        yas.weight3, ' +
            '        yas.weight4, ' +
            '        yas.weight5, ' +
            '        yas.threshold1, ' +
            '        yas.threshold2, ' +
            '        yas.threshold3, ' +
            '        yas.threshold4, ' +
            '        yas.threshold5 ' +
            ' FROM yjx.yjx_activity ya, ' +
            '      yjx.yjx_act_setting yas, ' +
            '      yjx.yjx_org yo ' +
            ' WHERE ya.act_id = ' + activityId +
            '   AND yo.org_id = ya.org_id ' +
            '   AND yas.as_id = ya.as_id ' +
            '   AND ya.del_flg = 0 ' +
            // '   AND ya.publish = 1 ' + // 后台使用时, 活动可能取消发布
            '   AND yas.del_flg = 0 ';
        console.log(sql);
        connection.query(sql, function(queryErr, results) {
            // And done with the connection.
            connection.release();
            if(queryErr || !results || results.length <=0) {
                console.log('YjxActivityModel.getSingleActivitySettingInfo error:'+queryErr);
                return callback(queryErr||'该活动不存在');
            }
            return callback(queryErr, results[0]);
        });

    });
};

/**
 * 获取活动的统计信息
 *
 * @author 王俊
 * @date 2018-06-08
 *
 * @param {Object} option 请求参数
 * @param {Number} [option.activityId] 指定活动 id, 若传递此字段, 则只查询该活动
 * @param {Array} [option.activityIds] 指定活动 id 数组, 若传递此字段, 则可查询该组活动
 * @param {Function} callback 回调函数
 */
YjxActivityModel.prototype.getActivityStatisticsInfo = function (option, callback) {
    if(!option.activityId && (!option.activityIds || option.activityIds.length <= 0)) {
        return callback('参数错误', null);
    }
    pool.pool.getConnection(function(getConErr, connection) {
        if (getConErr) {
            console.log('YjxActivityModel.getActivityStatisticsInfo Err:'+err);
            return callback(getConErr,null);
        }

        var actCondition = '      AND yaup.act_id = ' + option.activityId;
        if(option.activityIds && option.activityIds.length > 0) {
            actCondition = '      AND yaup.act_id in ( ' + option.activityIds.join(',') + ') ';
        }
        var sql =
            ' SELECT count(yaup.aup_id) AS totalProducts, ' +
            '        sum(yaup.data1) AS totalData1, ' +
            '        sum(yaup.data2) AS totalData2, ' +
            '   (SELECT count(DISTINCT (yaup.user_id)) ' +
            '    FROM yjx.yjx_act_user_product yaup ' +
            '    WHERE yaup.del_flg = 0 ' +
            actCondition +
            '      AND yaup.state = 30) AS totalUsers ' +
            ' FROM yjx.yjx_act_user_product yaup ' +
            ' WHERE yaup.del_flg = 0 ' +
            actCondition +
            '   AND yaup.state = 30 ';
        console.log(sql);
        connection.query(sql, function(queryErr, results) {
            // And done with the connection.
            connection.release();
            if(queryErr || !results || results.length <=0) {
                console.log('YjxActivityModel.getActivityStatisticsInfo error:'+queryErr);
                callback(queryErr||'暂无统计信息');
            }
            return callback(queryErr, results[0]);
        });

    });
};


/**
 * 获取活动列表sql
 *
 * @param bForCnt  标记是否需要分页
 * @param {Object}  param 筛选活动的参数列表
 * @param {Array}  param.states    活动状态数组10未开场、20已开场、30活动截止、40活动关闭、50活动取消 数组的json字符串
 * @param {Number} param.pageNum   页码
 * @param {Number} param.pageSize  页容量
 * @param {Number} [param.userId]  用户id
 * @param {Number} [param.filterUser] 是否根据用户过滤活动列表, 0 不过滤, 1 过滤, 如使用该字段必须传递 userId
 * @param {Number} [param.checkReward] 是否检测用户在活动中的获奖状况,0 不过滤, 1 过滤,  如使用该字段必须传递 userId
 * @param {Number} param.withTotalPage 是否需要返回总页数  0不需要  1需要
 * @param {Number} param.orgId 组织id该接口不适用该参数
 * @param {Number} param.flag 用来标记当前接口需要使用publish字段
 * @returns {string}
 */
function sql_getActivityList(bForCnt,param) {

    var sql = '';
    if(bForCnt) {
        sql += " SELECT " +
            "   COUNT(DISTINCT res.id) AS `count` ";
    } else  {
        sql += "SELECT DISTINCT *  " ;
    }
    sql +=  " FROM " +
        "	( SELECT	" +
        "	  act.act_id AS id,	" +
        "	  act.act_no AS actNo,	" +
        "	  act.title,	" +
        "	  act.theme_img AS thumb,	" +
        "	  act.url,	" +
        "	  yo.name AS orgName,	" +
        "	  DATE_FORMAT(act.start_time, '%Y.%c.%d') AS startDate,	" +
        "	  DATE_FORMAT(act.end_time, '%Y.%c.%d') AS endDate,	" +
        "	  UNIX_TIMESTAMP(act.start_time) AS startTimestamp,	" +
        "	  UNIX_TIMESTAMP(act.end_time) AS endTimestamp,	" +
        "	  CASE	" +
        "	    WHEN act.state > 30	" +
        "	    THEN act.state	" +
        "	    WHEN act.start_time > SYSDATE()	" +
        "	    THEN 10	" +
        "	    WHEN act.end_time <= SYSDATE()	" +
        "	    THEN 30	" +
        "	    ELSE 20	" +
        "	  END AS state,	" +
        "     IFNULL(yas.reward_total,0) AS rewardTotal, " +
        "     IFNULL(yast.show_view_cnt,0) AS viewCnt, " +
        "     act.order,  "+
        "     act.create_time  "+
        "	FROM	" ;
    if (param.userId && param.filterUser && param.userId>0){
        sql += " yjx.yjx_act_user_product yaup,";
    }
    sql +=   "	  yjx.yjx_org yo,	" +
        " yjx.yjx_activity act	" +
        "   LEFT JOIN yjx.yjx_act_setting yas ON yas.as_id = act.as_id  AND yas.del_flg = 0   " +
        "   LEFT JOIN yjx.yjx_act_stats yast ON act.act_id = yast.act_id  AND yast.del_flg = 0   " +
        "	WHERE  act.del_flg = 0	" ;
    if (param.userId && param.filterUser && param.userId>0){
        sql += " AND yaup.act_id = act.act_id" +
            "    AND yaup.del_flg = 0" +
            "    AND yaup.user_id = "+param.userId+" ";
    }
    if (param.flag){
        sql+=" AND act.publish = 1 "
    }
    sql += "	  AND yo.del_flg = 0	" +
        "	  AND act.type = 10	" +
        "	  AND act.org_id = yo.org_id	" ;
    if (param.orgId && param.orgId>0){
        sql += "  AND  yo.org_id = "+param.orgId+" ";
    }
    if(param.states && param.states.length > 0) {
        var  term = " AND (";
        for (var i = 0; i < param.states.length; i++) {
            var state = param.states[i];
            if (state == 10) {
                term += "   ( now() < act.start_time AND act.state < 40 ) or ";
            }
            if (state == 20) {
                term += "  ( (now() > act.start_time and now() < act.end_time ) AND act.state < 40 ) or ";
            }
            if (state == 30) {
                term += "  (now() > act.end_time AND act.state < 40 ) or ";
            }
            if (state == 40) {
                term += "  act.state = 40 or ";
            }
            if (state == 50) {
                term += "  act.state = 50  or ";
            }
            if (state == 60) {
                term += "  act.state = 60  or ";
            }
        }
        term = term.substring(0, term.lastIndexOf('or '));
        term += " )";
        sql += term;
    }
    sql += " ) res " ;
    sql +="  ORDER BY res.order ASC,res.create_time DESC " ;
    if (!bForCnt) {
        if (typeof param.pageNum !== 'undefined' && param.pageNum !== null &&
            typeof param.pageSize !== 'undefined' && param.pageSize !== null)
        {
            var skipCnt = Number(param.pageNum||'0') * Number(param.pageSize||'0');
            sql += " LIMIT " + skipCnt + "," + Number(param.pageSize||'0');
        }
    }
    return sql;
}


/**
 * 获取活动列表
 *
 * @author 毛凯
 * @date 2018-6-11
 *
 * @param {Object}  param 筛选活动的参数列表
 * @param {Array}  param.states    活动状态数组10未开场、20已开场、30活动截止、40活动关闭、50活动取消 数组的json字符串
 * @param {Number} param.pageNum   页码
 * @param {Number} param.pageSize  页容量
 * @param {Number} [param.userId]  用户id
 * @param {Number} [param.filterUser] 是否根据用户过滤活动列表, 0 不过滤, 1 过滤, 如使用该字段必须传递 userId
 * @param {Number} [param.checkReward] 是否检测用户在活动中的获奖状况,0 不过滤, 1 过滤,  如使用该字段必须传递 userId
 * @param {Number} param.withTotalPage 是否需要返回总页数  0不需要  1需要
 * @param {Number} param.orgId 组织id该接口不适用该参数
 * @param {Number} param.flag 用来标记当前接口需要使用publish字段
 * @param callback 回调
 *           err: 查询失败
 *           results: 活动列表
 *
 * */
YjxActivityModel.prototype.getActivityList = function (param,callback){
    pool.pool.getConnection(function (getConErr, connection) {
        if (getConErr) {
            console.log('YjxActivityModel.js [getActivityList] getConErr' + getConErr);
            return callback(getConErr, null);
        }
        var sql = sql_getActivityList(false,param);
        console.log(sql);
        connection.query(sql, function (queryErr, results) {
            connection.release();
            if (queryErr) {
                console.log('YjxActivityModel.js [getActivityList] queryErr' + queryErr)
            }
            return callback(queryErr, results);
        });
    })
};
/**
 * 获取活动数量
 *
 * @author 毛凯
 * @date 2018-6-11
 *
 * @param {Object}  param 筛选活动的参数列表
 * @param {Array}  param.states    活动状态数组10未开场、20已开场、30活动截止、40活动关闭、50活动取消 数组的json字符串
 * @param {Number} param.pageNum   页码
 * @param {Number} param.pageSize  页容量
 * @param {Number} [param.userId]  用户id
 * @param {Number} [param.filterUser] 是否根据用户过滤活动列表, 0 不过滤, 1 过滤, 如使用该字段必须传递 userId
 * @param {Number} [param.checkReward] 是否检测用户在活动中的获奖状况,0 不过滤, 1 过滤,  如使用该字段必须传递 userId
 * @param {Number} param.withTotalPage 是否需要返回总页数  0不需要  1需要
 * @param {Number} param.orgId 组织id该接口不适用该参数
 * @param {Number} param.flag 用来标记当前接口需要使用publish字段
 * @param callback 回调
 *           err: 查询失败
 *           results: 活动列表
 *
 * */
YjxActivityModel.prototype.getActivityListCnt = function (param,callback){
    pool.pool.getConnection(function (getConErr, connection) {
        if (getConErr) {
            console.log('YjxActivityModel.js [getActivityListCnt] getConErr' + getConErr);
            return callback(getConErr, null);
        }
        var sql = sql_getActivityList(true,param);
        console.log(sql);
        connection.query(sql, function (queryErr, results) {
            connection.release();
            if (queryErr) {
                console.log('YjxActivityModel.js [getActivityListCnt] queryErr' + queryErr)
            }
            return callback(queryErr, results[0].count);
        });
    })
};


//***************************************************************************************************************
/**
 * 获取活动列表sql
 *
 * @param bForCnt  标记是否需要分页
 * @param {Object} param 筛选活动的参数列表
 * @param {Number} param.pageNum   页码
 * @param {Number} param.pageSize  页容量
 * @param {Array}  param.findStates 活动状态数组[10未开场、20已开场、30活动截止、40活动关闭、50活动取消、60活动筹备中]
 * @param {Number} [param.withRewardInfo] 是否返回活动奖励信息
 * @param {Number} param.coopOrgId 合作组织id(如网红公司)
 * @returns {string}
 */
function sql_getActList_2(bForCnt,param) {

    var sql = '';
    if(bForCnt) {
        sql += " SELECT COUNT(xx.act_id) AS `count` ";
    } else  {
        sql += " SELECT xx.* " ;
    }
    sql +=  " FROM " +
        " ( SELECT " +
        "	act.act_id,act.act_no,act.type,act.org_id,r.`name`,act.as_id,act.state,act.title, " +
        "	DATE_FORMAT(act.start_time, '%Y-%m-%d %H:%i:%s') AS start_time, " +
        "	DATE_FORMAT(act.end_time, '%Y-%m-%d %H:%i:%s') AS end_time, " +
        " 	case when act.state>30 then act.state " +
        " 		 when act.start_time>sysdate() then 10 " +
        " 	     when act.end_time<=sysdate() then 30 " +
        " 	     else 20 " +
        " 	end AS valid_state, " +
        " 	act.desc,act.theme_img,act.images,act.url,act.`order`,act.publish, " +
        " 	DATE_FORMAT(act.create_time,'%y/%m/%d %H:%i:%s') AS create_time, " +
        " 	DATE_FORMAT(act.update_time,'%y/%m/%d %H:%i:%s') AS update_time ";

    // 奖励信息
    if(param.withRewardInfo) {
        // reward1: 已发放的奖励
        sql += " ,IFNULL( " +
            " 	(SELECT SUM(IFNULL(aur.`bal_reward`,0)) " +
            " 	FROM yjx.yjx_act_user_reward aur  " +
            " 	WHERE aur.act_id = act.act_id ";
        if(param.coopOrgId && param.coopOrgId>0) {
            sql += " AND aur.org_id= "+param.coopOrgId;
        }
        sql += " 	AND aur.bal_flg=1 " +
            " 	AND aur.r_type IN (10) " +// 奖励类型：0未知，10挑战活动奖励，20发放推广码奖励, 21填写推广码奖励
            " 	AND aur.del_flg=0) " +
            " 	, 0) AS reward1 ";
        // reward2: 已提现的奖励
        sql += " ,IFNULL( " +
            " 	(SELECT SUM(IFNULL(afh.`cash_amt`,0)) " +
            " 	FROM yjx.yjx_act_user_reward aur " +
            " 	INNER JOIN yjx.yjx_act_finance_his afh " +
            " 		ON afh.aur_id=aur.aur_id " +
            " 		AND afh.state=10  " +
            " 		AND afh.cash_state=20  " +
            " 		AND afh.del_flg=0 " +
            " 	WHERE aur.act_id = act.act_id ";
        if(param.coopOrgId && param.coopOrgId>0) {
            sql += " AND aur.org_id= "+param.coopOrgId;
        }
        sql += " 	AND aur.bal_flg=2 " +
            " 	AND aur.del_flg=0) " +
            " 	, 0) AS reward2 ";
    }

    sql += " FROM " +
        " 	yjx.yjx_activity act " +
        " 	LEFT JOIN yjx.yjx_org r " +
        " 	    ON r.org_id = act.org_id and r.del_flg=0 " +
        " WHERE " +
        " 	act.del_flg = 0 ";
    if(param.actId && param.actId>0) {
        sql += " 	and act.act_id= "+param.actId;
    }
    if(param.type && param.type>0) {
        sql += " 	and act.type= "+param.type;
    }
    if(param.state && param.state>0) {
        sql += " 	and act.state= "+param.state;
    }
    if(param.title && (param.title||'').length>0) {
        sql += " 	and act.title like CONCAT('%','" +param.title+ "','%') ";
    }
    if(param.hasOwnProperty('publish')) {
        sql += "	and act.publish= "+param.publish;
    }
    if(param.coopOrgId && param.coopOrgId>0) {
        sql += " AND act.`act_id` IN (" +
            " 	SELECT DISTINCT p.act_id " +
            " 	FROM yjx.yjx_act_user_product p " +
            " 	WHERE p.del_flg=0 " +
            " 	AND p.state=30 " +
            " 	AND p.org_id= "+param.coopOrgId +") ";
    }
    sql += " ) xx ";

    // 准确活动状态不直接用"state"字段判断, 用sql中的"valid_state"判断
    if(param.findStates && param.findStates.length>0) {
        sql += " WHERE xx.valid_state IN ("+(param.findStates.join(','))+") ";
    }

    sql += " ORDER BY xx.order, xx.act_id DESC " ;
    if (!bForCnt) {
        if (typeof param.pageNum !== 'undefined' && param.pageNum !== null &&
            typeof param.pageSize !== 'undefined' && param.pageSize !== null)
        {
            var skipCnt = Number(param.pageNum||'0') * Number(param.pageSize||'0');
            sql += " LIMIT " + skipCnt + "," + Number(param.pageSize||'0');
        }
    }
    return sql;
}
// 获取活动列表Cnt
YjxActivityModel.prototype.getActListCnt_2 = function (param,callback){
    pool.pool.getConnection(function (getConErr, connection) {
        if (getConErr) {
            console.log('YjxActivityModel.js [getActListCnt_2] getConErr' + getConErr);
            return callback(getConErr, null);
        }
        var sql = sql_getActList_2(true,param);
        console.log(sql);
        connection.query(sql, function (queryErr, results) {
            connection.release();
            if (queryErr) {
                console.log('YjxActivityModel.js [getActListCnt_2] queryErr' + queryErr)
            }
            return callback(queryErr, results[0].count);
        });
    })
};
// 获取活动列表信息
YjxActivityModel.prototype.getActList_2 = function (param,callback){
    pool.pool.getConnection(function (getConErr, connection) {
        if (getConErr) {
            console.log('YjxActivityModel.js [getActList_2] getConErr' + getConErr);
            return callback(getConErr, null);
        }
        var sql = sql_getActList_2(false,param);
        console.log(sql);
        connection.query(sql, function (queryErr, results) {
            connection.release();
            if (queryErr) {
                console.log('YjxActivityModel.js [getActList_2] queryErr' + queryErr)
            }
            return callback(queryErr, results);
        });
    })
};
//***************************************************************************************************************


/**
 * 统计用户获奖作品的个数与获奖的总奖金
 *
 * @author 毛凯
 * @date 2018-6-11
 *
 * @param activityId 活动id
 * @param userId  用户id
 * @param callback 回调
 *           err: 查询失败
 *           results: 活动列表
 *
 * */
YjxActivityModel.prototype.getUserRewardCntAndUserDoc = function (activityId,userId,callback){
    pool.pool.getConnection(function (getConErr, connection) {
        if (getConErr) {
            console.log('YjxActivityModel.js [getActivityListCnt] getConErr' + getConErr);
            return callback(getConErr, null);
        }
        var sql = "	select	" +
            "	  a.rewardProductCnt,	" +
            "	  b.totalRewardAmount	" +
            "	from	" +
            "	  (select	" +
            "	    count(user_id) as rewardProductCnt	" +
            "	  from	" +
            "	    yjx.yjx_act_user_product yaup	" +
            "	  where yaup.user_id = "+userId+"	" +
            "	    and act_id = "+activityId+"	" +
            "	    and yaup.del_flg = 0	" +
            "       AND yaup.state  = 30 " +
            "	    and yaup.reward_amt > 0) a,	" +
            "	  (SELECT	" +
            "	    SUM(yaup.reward_amt) AS totalRewardAmount	" +
            "	  FROM	" +
            "	    yjx.yjx_act_user_product yaup	" +
            "	  WHERE yaup.del_flg = 0	" +
            "       AND yaup.state  = 30  " +
            "	    AND yaup.act_id = "+activityId+"	" +
            "	    AND yaup.user_id = "+userId+" ) b	" ;
        console.log(sql);
        connection.query(sql, function (queryErr, results) {
            connection.release();
            if (queryErr) {
                console.log('YjxActivityModel.js [getActivityListCnt] queryErr' + queryErr)
            }
            return callback(queryErr, results);
        });
    })
};

/**
 * 统计组织下所有发布活动以及参加所有作品
 *
 * @author 毛凯
 * @date 2018-6-11
 *
 * @param orgId 组织id
 * @param callback 回调
 *           err: 查询失败
 *           results: 活动列表
 *
 * */
YjxActivityModel.prototype.getOrgActivityCntAndProductCnt = function (orgId,callback){
    pool.pool.getConnection(function (getConErr, connection) {
        if (getConErr) {
            console.log('YjxActivityModel.js [getActivityListCnt] getConErr' + getConErr);
            return callback(getConErr, null);
        }
        var sql = "	SELECT 	" +
            "	 a.*,	" +
            "	 b.*	" +
            "	 FROM	" +
            "	(SELECT	" +
            "	  COUNT(ya.act_id) AS totalActivity	" +
            "	FROM	" +
            "	  yjx.yjx_activity ya	" +
            "	WHERE ya.org_id = "+orgId+"	" +
            "	AND ya.del_flg = 0) a,	" +
            "	(SELECT	" +
            "	  COUNT(yaup.aup_id) AS totalProducts	" +
            "	FROM	" +
            "	  yjx.yjx_activity ya,	" +
            "	  yjx.yjx_act_user_product yaup	" +
            "	WHERE ya.org_id = "+orgId+"	" +
            "	  AND ya.act_id = yaup.act_id	" +
            "	  AND ya.del_flg = 0	" +
            "	  AND yaup.state = 30	" +
            "	  AND yaup.del_flg = 0) b	" ;
            console.log(sql);
        connection.query(sql, function (queryErr, results) {
            connection.release();
            if (queryErr) {
                console.log('YjxActivityModel.js [getActivityListCnt] queryErr' + queryErr)
            }
            return callback(queryErr, results);
        });
    })
};

/**
 * 获取组织下所有活动id
 *
 * @author 毛凯
 * @date 2018-6-11
 *
 * @param orgId  组织id
 * @param callback 回调
 *           err: 查询失败
 *           results: 活动列表
 *
 * */
YjxActivityModel.prototype.getOrgActivityAllId = function (orgId,callback){
    pool.pool.getConnection(function (getConErr, connection) {
        if (getConErr) {
            console.log('YjxActivityModel.js [getActivityListCnt] getConErr' + getConErr);
            return callback(getConErr, null);
        }
        var sql = "	SELECT	" +
                "	  ya.act_id	" +
                "	FROM	" +
                "	  yjx.yjx_activity ya	" +
                "	WHERE  ya.del_flg = 0	" ;
        if(orgId && orgId > 0){
            sql +=  "   AND ya.org_id = "+orgId+" ";
        }
            console.log(sql);
        connection.query(sql, function (queryErr, results) {
            connection.release();
            if (queryErr) {
                console.log('YjxActivityModel.js [getActivityListCnt] queryErr' + queryErr)
            }
            return callback(queryErr, results);
        });
    })
};

/**
 * 统计每个活动中参与的总人数
 *
 * @author 毛凯
 * @date 2018-6-11
 *
 * @param orgId  组织id
 * @param actId  活动id
 * @param callback 回调
 *           err: 查询失败
 *           results: 活动列表
 *
 * */
YjxActivityModel.prototype.getOrgActivityAllUser = function (orgId,actId,callback){
    pool.pool.getConnection(function (getConErr, connection) {
        if (getConErr) {
            console.log('YjxActivityModel.js [getOrgActivityAllUser] getConErr' + getConErr);
            return callback(getConErr, null);
        }
        var sql = "	SELECT	" +
                "	  COUNT(DISTINCT yaup.user_id) as `count`	" +
                "	FROM	" +
                "	  yjx.yjx_activity ya,	" +
                "	  yjx.yjx_act_user_product yaup	" +
                "	WHERE ya.org_id = "+orgId+"	" +
                "	AND ya.act_id  = "+actId+"	" +
                "	AND yaup.state  = 30	" +
                "	AND ya.act_id = yaup.act_id 	" ;
            console.log(sql);
        connection.query(sql, function (queryErr, results) {
            connection.release();
            if (queryErr) {
                console.log('YjxActivityModel.js [getActivityListCnt] queryErr' + queryErr)
            }
            return callback(queryErr, results[0].count);
        });
    })
};

/**
 * 统计每个活动中参与的总人数
 *
 * @author 毛凯
 * @date 2018-6-11
 *
 * @param actId  活动id
 * @param callback 回调
 *           err: 查询失败
 *           results: 活动列表
 *
 * */
YjxActivityModel.prototype.getOrgActivityCntInfo = function (actId,callback){
    pool.pool.getConnection(function (getConErr, connection) {
        if (getConErr) {
            console.log('YjxActivityModel.js [getOrgActivityCntInfo] getConErr' + getConErr);
            return callback(getConErr, null);
        }
        var sql = "	SELECT	" +
                "	  a.totalUser,	" +
                "	  b.totalProducts	" +
                "	FROM	" +
                "	  (SELECT	" +
                "	    COUNT(DISTINCT yaup.user_id) AS totalUser	" +
                "	  FROM	" +
                "	    yjx.yjx_act_user_product yaup	" +
                "	  WHERE yaup.act_id = "+actId+"	" +
                "	    AND yaup.state = 30	" +
                "	    AND yaup.del_flg = 0) a,	" +
                "	  (SELECT	" +
                "	    COUNT(yaup.aup_id) AS totalProducts	" +
                "	  FROM	" +
                "	    yjx.yjx_act_user_product yaup	" +
                "	  WHERE yaup.del_flg = 0	" +
                "	    AND yaup.state = 30	" +
                "	    AND yaup.act_id = "+actId+") b	" ;
            console.log(sql);
        connection.query(sql, function (queryErr, results) {
            connection.release();
            if (queryErr) {
                console.log('YjxActivityModel.js [getActivityListCnt] queryErr' + queryErr)
            }
            return callback(queryErr, results);
        });
    })
};


/**
 * 关闭活动
 *
 * @author 毛凯
 * @date 2018-6-14
 *
 * @param actId  活动id
 * @param callback 回调
 *           err: 查询失败
 *           results: 活动列表
 *
 * */
YjxActivityModel.prototype.updActivityState = function (actId,callback){
    pool.pool.getConnection(function (getConErr, connection) {
        if (getConErr) {
            console.log('YjxActivityModel.js [getOrgActivityAllUser] getConErr' + getConErr);
            return callback(getConErr, null);
        }
        var sql = " UPDATE " +
               "  yjx.yjx_activity ya " +
               " SET "+
               "    ya.state = 40 " +
               " WHERE act_id = "+actId+" " +
              "  AND ya.del_flg = 0 ";
        console.log(sql);
        connection.query(sql, function (queryErr, results) {
            connection.release();
            if (queryErr) {
                console.log('YjxActivityModel.js [getActivityListCnt] queryErr' + queryErr)
            }
            return callback(queryErr, results);
        });
    })
};

/**
 * 获取活动的推广码配置信息
 *
 * @author 毛凯
 * @date 2018-7-4
 *
 * @param actId  活动id
 * @param callback 回调
 *           err: 查询失败
 *           results: 活动列表
 *
 * */
YjxActivityModel.prototype.getActivityCouponInfo = function (actId,callback){
    pool.pool.getConnection(function (getConErr, connection) {
        if (getConErr) {
            console.log('YjxActivityModel.js [getOrgActivityAllUser] getConErr' + getConErr);
            return callback(getConErr, null);
        }
        var sql = "	SELECT	" +
            "	  ya.act_id,	" +
            "	  ya.title,	" +
            "	  yarf.max,	" +
            "	  yarf.reward_amt	" +
            "	FROM	" +
            "	  yjx.yjx_activity ya,	" +
            "	  yjx.yjx_act_setting yas,	" +
            "	  yjx.yjx_act_reward_rule yarf	" +
            "	WHERE ya.as_id = yas.as_id	" +
            "	AND yas.as_id = yarf.as_id	" +
            "	AND yarf.type = 20	" +
            "	AND ya.del_flg = 0 	" +
            "	AND yas.del_flg = 0	" +
            "	AND yarf.del_flg = 0	" ;
        if (actId && actId>0){
            sql+= "	AND ya.act_id = "+actId+"	" ;
        }
            console.log(sql);
        connection.query(sql, function (queryErr, results) {
            connection.release();
            if (queryErr) {
                console.log('YjxActivityModel.js [getActivityListCnt] queryErr' + queryErr)
            }
            return callback(queryErr, results);
        });
    })
};


/**
 * 获取活动为使用推广码发放奖励的用户作品列表
 *
 * @author 王俊
 * @date 2018-7-10
 *
 * @param options
 * @param options.actId  活动id
 * @param options.limit  限制人数
 * @param callback 回调
 *           err: 查询失败
 *           results: 作品列表
 * */
YjxActivityModel.prototype.getDistributeIncomeForUserUsePromoterCodeList = function (options, callback){
    pool.pool.getConnection(function (getConErr, connection) {
        if (getConErr) {
            console.log('YjxActivityModel.js [getDistributeIncomeForUserUsePromoterCodeList] getConErr' + getConErr);
            return callback(getConErr, null);
        }
        var sql =
            ' SELECT * ' +
            ' FROM ' +
            '   ( SELECT yaup.act_id, ' +
            '            yaup.user_id, ' +
            '            yaup.org_id, ' +
            '            MIN(yaup.aup_id) AS valid_aup_id, ' +
            '            yaup.create_time ' +
            '    FROM yjx.yjx_act_user_product yaup ' +
            '    INNER JOIN yjx.yjx_coupon yc ON yc.c_code=yaup.c_code ' +
            '    AND yc.state=20 ' +
            '    AND yc.user_id>0 ' +
            '    AND yc.del_flg=0 ' +
            '    WHERE yaup.del_flg=0 ' +
            '      AND yaup.state=30 ' +
            '      AND yaup.user_id !=  yc.user_id ' +
            '      AND yaup.act_id = ' + options.actId +
            '      AND yaup.org_id = 0 ' +
            '    GROUP BY yaup.act_id, ' +
            '             yaup.user_id) vp ' +
            ' WHERE ' +
            '     (SELECT count(0) ' +
            '      FROM yjx.yjx_act_user_reward yaur ' +
            '      WHERE yaur.act_id = vp.act_id ' +
            '        AND yaur.r_type = 21 ' +
            '        AND yaur.del_flg = 0 ' +
            '        AND yaur.user_id = vp.user_id) <= 0 ' +
            ' ORDER BY vp.create_time LIMIT 0, ' + options.limit ;
        console.log(sql);
        connection.query(sql, function (queryErr, results) {
            connection.release();
            if (queryErr) {
                console.log('YjxActivityModel.js [getDistributeIncomeForUserUsePromoterCodeList] queryErr' + queryErr)
            }
            return callback(queryErr, results);
        });
    })
};




/**
 * 获取拥有推荐码通过审核的作品奖励信息列表
 *
 * @author 毛凯
 * @date 2018-7-10
 *
 * @param options
 * @param options.actId  活动id
 * @param options.limit  限制人数
 * @param callback 回调
 *           err: 查询失败
 *           results: 作品列表
 * */
YjxActivityModel.prototype.getOwnCcodeUserPromoterCodeList = function (options, callback){
    pool.pool.getConnection(function (getConErr, connection) {
        if (getConErr) {
            console.log('YjxActivityModel.js [getDistributeIncomeForUserUsePromoterCodeList] getConErr' + getConErr);
            return callback(getConErr, null);
        }
        var sql =
            "	SELECT	" +
            "	  *	" +
            "	FROM	" +
            "	  (SELECT	" +
            "	    yaup.act_id,	" +
            "	    yaup.user_id,	" +
            "	    yaup.aup_id,	" +
            "	    yc.user_id AS uid,	" +
            "	    CASE	" +
            "	      WHEN yc.use >= 20	" +
            "	      THEN 0	" +
            "	      ELSE yc.org_id	" +
            "	    END AS org_id,	" +
            "	    MIN(yaup.aup_id) AS valid_aup_id,	" +
            "	    yaup.create_time	" +
            "	  FROM	" +
            "	    yjx.yjx_act_user_product yaup	" +
            "	    INNER JOIN yjx.yjx_coupon yc	" +
            "	      ON yc.c_code = yaup.c_code	" +
            "	      AND yc.state = 20	" +
            "	      AND yc.user_id > 0	" +
            "	      AND yc.del_flg = 0	" +
            "	  WHERE yaup.del_flg = 0	" +
            "	    AND yaup.state = 30	" +
            "	    AND yaup.user_id != yc.user_id	" +
            "	    AND yaup.act_id = 	" +options.actId+
            "	    AND yaup.org_id = 0	" +
            "	  GROUP BY yaup.act_id,	" +
            "	    yaup.user_id) vp	" +
            "	WHERE	" +
            "	  (SELECT	" +
            "	    COUNT(0)	" +
            "	  FROM	" +
            "	    yjx.yjx_act_user_reward yaur	" +
            "	  WHERE yaur.act_id = vp.act_id	" +
            "	    AND yaur.r_type = 20	" +
            "	    AND yaur.del_flg = 0	" +
            "	     AND yaur.aup_id = vp.aup_id) <= 0	" +
            " ORDER BY vp.create_time LIMIT 0, " + options.limit ;
        console.log(sql);
        connection.query(sql, function (queryErr, results) {
            connection.release();
            if (queryErr) {
                console.log('YjxActivityModel.js [getDistributeIncomeForUserUsePromoterCodeList] queryErr' + queryErr)
            }
            return callback(queryErr, results);
        });
    })
};

/**
 * 获取活动的推荐码相关配置信息
 *
 * @author 毛凯
 * @date 2018-7-10
 *
 * @param  actId  活动id
 * @param  type  配置的类型
 * @param callback 回调
 *           err: 查询失败
 *           results: 作品列表
 * */
YjxActivityModel.prototype.getActivityCcodeConfigInfo= function (actId,type, callback){
    pool.pool.getConnection(function (getConErr, connection) {
        if (getConErr) {
            console.log('YjxActivityModel.js [getDistributeIncomeForUserUsePromoterCodeList] getConErr' + getConErr);
            return callback(getConErr, null);
        }
        var sql ="	SELECT	" +
                "	  *	" +
                "	FROM	" +
                "	  yjx.yjx_activity ya,	" +
                "	  yjx.yjx_act_setting yas	" +
                "	  LEFT JOIN yjx.yjx_act_reward_rule yarr	" +
                "	    ON yarr.as_id = yas.as_id	" +
                "	    AND yarr.del_flg = 0	" +
                "	    AND yas.del_flg = 0	" +
                "	    AND yarr.state = 10	" +
                "	    AND yarr.type = "+type+"	" +
                "	WHERE ya.act_id = "+actId+"	" +
                "	  AND ya.as_id = yas.as_id	" +
                "	  AND yarr.del_flg = 0	" +
                "	  AND yas.del_flg = 0	" ;
            console.log(sql);
        connection.query(sql, function (queryErr, results) {
            connection.release();
            if (queryErr) {
                console.log('YjxActivityModel.js [getActivityCcodeConfigInfo] queryErr' + queryErr)
            }
            return callback(queryErr, results);
        });
    })
};



/**
 * 获取活动推荐方排行榜
 *
 * @param bForCnt  标记是否需要分页
 * @param {Object}  param 筛选活动的参数列表
 * @param {Number}  param.actId  活动id
 * @param {Number} param.order   排序方式
 * @param {Number} param.pageNum  页码
 * @param {Number} param.pageSize  页容量
 * @param {Number} param.withTotalPage 是否需要分页
 * @returns {string}
 */
function sql_getActivityAllUserSendCodeRewardList(bForCnt,param) {

    var sql = '';
    if(bForCnt) {
        sql += " SELECT " +
            "   COUNT(0) AS `count`, "+
            "	  SUM(bal_reward) AS promoterRewaed	" ;
    } else  {
        sql += "	SELECT	" +
            "	  COUNT(0) AS promoted,	" +
            "	  SUM(yaur.bal_reward) AS promoterRewaed,	" +
            "	  IFNULL(	" +
            "	    (SELECT	" +
            "	      ytpu.nickname	" +
            "	    FROM	" +
            "	      yjx.yjx_third_party_user ytpu	" +
            "	    WHERE ytpu.user_id = yaur.user_id	" +
            "	      AND del_flg = 0	" +
            "	      AND (	" +
            "	        ytpu.account_type BETWEEN 200	" +
            "	        AND 300	" +
            "	      )	" +
            "	    LIMIT 0, 1),	" +
            "	    (SELECT	" +
            "	      yu.nickname	" +
            "	    FROM	" +
            "	      yjx.yjx_user yu	" +
            "	    WHERE yu.user_id = yaur.user_id	" +
            "	      AND yu.del_flg = 0)	" +
            "	  ) AS nickName,	" +
            "	  IFNULL(	" +
            "	    (SELECT	" +
            "	      ytpu.profile_photo	" ;
    }
    sql +="	    FROM	" +
        "	      yjx.yjx_third_party_user ytpu	" +
        "	    WHERE ytpu.user_id = yaur.user_id	" +
        "	      AND del_flg = 0	" +
        "	      AND (	" +
        "	        ytpu.account_type BETWEEN 200	" +
        "	        AND 300	" +
        "	      )	" +
        "	    LIMIT 0, 1),	" +
        "	    (SELECT	" +
        "	      yu.profile_photo	" +
        "	    FROM	" +
        "	      yjx.yjx_user yu	" +
        "	    WHERE yu.user_id = yaur.user_id	" +
        "	      AND yu.del_flg = 0)	" +
        "	  ) AS wxProfilePhoto	" +
        "	FROM	" +
        "	  yjx.yjx_act_user_reward yaur	" +
        "	WHERE yaur.act_id = "+param.actId+"	" +
        "	  AND yaur.del_flg = 0	" +
        "	  AND yaur.r_type = 20	" +
        "	GROUP BY yaur.user_id	" ;
    if (param.order === 1) {
        sql += "	ORDER BY promoterRewaed DESC,	" +
            "	  yaur.create_time ASC	" ;
    }
    if (!bForCnt) {
        if (typeof param.pageNum !== 'undefined' && param.pageNum !== null &&
            typeof param.pageSize !== 'undefined' && param.pageSize !== null)
        {
            var skipCnt = Number(param.pageNum||'0') * Number(param.pageSize||'0');
            sql += " LIMIT " + skipCnt + "," + Number(param.pageSize||'0');
        }
    }
    return sql;
}





/**
 * 获取活动推荐方排行榜
 *
 * @author 毛凯
 * @date 2018-7-20
 * @param  options.actId  活动id
 * @param  options.order  排序方式
 * @param  options.pageNum 页码
 * @param  options.pageSize 页容量
 * @param  options.withTotalPage 是否需要分页
 * @param callback 回调
 *           err: 查询失败
 *           results: 作品列表
 * */
YjxActivityModel.prototype.getActivityAllUserSendCodeRewardList= function (options, callback){
    pool.pool.getConnection(function (getConErr, connection) {
        if (getConErr) {
            console.log('YjxActivityModel.js [getDistributeIncomeForUserUsePromoterCodeList] getConErr' + getConErr);
            return callback(getConErr, null);
        }
        var sql = sql_getActivityAllUserSendCodeRewardList(false,options);

            console.log(sql);
        connection.query(sql, function (queryErr, results) {
            connection.release();
            if (queryErr) {
                console.log('YjxActivityModel.js [getActivityCcodeConfigInfo] queryErr' + queryErr)
            }
            return callback(queryErr, results);
        });
    })
};

/**
 * 获取活动推荐方排行榜总个数
 *
 * @author 毛凯
 * @date 2018-7-20
 * @param  options.actId  活动id
 * @param  options.order  排序方式
 * @param  options.pageNum 页码
 * @param  options.pageSize 页容量
 * @param  options.withTotalPage 是否需要分页
 * @param callback 回调
 *           err: 查询失败get202MiniProgramActivityInfo
 *           results: 作品列表
 * */
YjxActivityModel.prototype.getActivityAllUserSendCodeRewardCnt= function (options, callback){
    pool.pool.getConnection(function (getConErr, connection) {
        if (getConErr) {
            console.log('YjxActivityModel.js [getDistributeIncomeForUserUsePromoterCodeList] getConErr' + getConErr);
            return callback(getConErr, null);
        }
        var sql =sql_getActivityAllUserSendCodeRewardList(true,options);

            console.log(sql);
        connection.query(sql, function (queryErr, results) {
            connection.release();
            if (queryErr) {
                console.log('YjxActivityModel.js [getActivityCcodeConfigInfo] queryErr' + queryErr)
            }
            if (!results || !results[0] || results.length<=0 ){
                return callback(queryErr, 0);
            } else{
                return callback(queryErr, results[0].count);
            }
        });
    })
};



/**
 * 获取图片点赞活动列表 sql
 *
 * @author 王俊
 * @date 2018-07-30
 *
 * @param beForCnt 是否用雨获取作品个数
 * @param {{Object}} options
 * @param {Array} options.states
 * @param {Number} options.pageNum
 * @param {Number} options.pageSize
 */
function sql_getImagePraiseActivityList(beForCnt, options) {
    var sql = '';
    if (beForCnt) {
        sql += ' SELECT COUNT(0) AS count';
    } else {
        sql += ' SELECT * ';
    }
    sql +=
    ' FROM yjx.yjx_activity ya ' +
    ' WHERE ya.type = 20 ' +
    '   AND ya.publish = 1 ' +
    '   AND ya.del_flg = 0 ';

    if(options.states.length >0) {
        sql += '   AND ( ';
    }
    var needOr = false;
    for (var i = 0; i < options.states.length; i++) {
        var state = Number(options.states[i]);
        
        // 未开场
        if(state === 10) {
            if(needOr) sql += ' OR ';
            sql += '        (now() < ya.start_time AND ya.state < 30) ';
            needOr = true;
        }
        // 已开场
        if(state === 20) {
            if(needOr) sql += ' OR ';
            sql += '        (now() BETWEEN ya.start_time AND ya.end_time AND ya.state < 30) ';
            needOr = true;
        }
        // 已截止
        if(state === 30) {
            if(needOr) sql += ' OR ';
            sql += '         (now() > ya.end_time ' + '            OR ya.state = 30) ';
            needOr = true;
        }
        // 已关闭
        if(state === 40) {
            if(needOr) sql += ' OR ';
            sql += '        ya.state = 40 ';
            needOr = true;
        }
        // 已取消
        if(state === 50) {
            if(needOr) sql += ' OR ';
            sql += '        ya.state = 50 ';
            needOr = true;
        }
        // 筹备中
        if (state === 60) {
            if(needOr) sql += ' OR ';
            sql += '        ya.state = 60 ';
            needOr = true;
        }
    }
    if(options.states.length >0) {
        sql += '   ) ';
    }
    if (!beForCnt) {
        sql += ' ORDER BY ya.order, ya.create_time DESC ';
        if (typeof options.pageNum !== 'undefined' && options.pageNum !== null &&
            typeof options.pageSize !== 'undefined' && options.pageSize !== null)
        {
            var skipCnt = Number(options.pageNum||'0') * Number(options.pageSize||'0');
            sql += " LIMIT " + skipCnt + "," + Number(options.pageSize||'0');
        }
    }
    return sql;
}

/**
 * 获取图片点赞活动列表
 *
 * @author 王俊
 * @date 2018-07-30
 *
 * @param {{Object}} options
 * @param {Array} options.states
 * @param {Number} options.pageNum
 * @param {Number} options.pageSize
 * @param {Function} callback
 *                      error
 *                      list
 */
YjxActivityModel.prototype.getImagePraiseActivityList = function (options, callback) {
    pool.pool.getConnection(function (getConErr, connection) {
        if (getConErr) {
            console.log('YjxActivityModel.js [getImagePraiseActivityList] getConErr' + getConErr);
            return callback(getConErr, null);
        }
        var sql = sql_getImagePraiseActivityList(false, options);
        console.log(sql);
        connection.query(sql, function (queryErr, results) {
            connection.release();
            if (queryErr) {
                console.log('YjxActivityModel.js [getImagePraiseActivityList] queryErr' + queryErr)
            }
            return callback(queryErr, results);
        });
    })
};

/**
 * 获取图片点赞活动列表
 *
 * @author 王俊
 * @date 2018-07-30
 *
 * @param {{Object}} options
 * @param {Array} options.states
 * @param {Function} callback
 *                      error
 *                      count
 */
YjxActivityModel.prototype.getImagePraiseActivityListCnt = function (options, callback) {
    pool.pool.getConnection(function (getConErr, connection) {
        if (getConErr) {
            console.log('YjxActivityModel.js [getImagePraiseActivityListCnt] getConErr' + getConErr);
            return callback(getConErr, null);
        }
        var sql = sql_getImagePraiseActivityList(true, options);
        console.log(sql);
        connection.query(sql, function (queryErr, results) {
            connection.release();
            if (queryErr || !results || results.length <=0) {
                return callback(queryErr || '查询总活动个数失败', 0);
                console.log('YjxActivityModel.js [getImagePraiseActivityListCnt] queryErr' + queryErr)
            }
            return callback(queryErr, results[0].count);
        });
    })
};




/**
 * 获取202小程序活动信息
 *
 * @author 毛凯
 * @date 2018-7-20
 * @param  actId  活动id
 * @param callback 回调
 *           err: 查询失败
 *           results: 作品列表
 * */
YjxActivityModel.prototype.get202MiniProgramActivityInfo= function (actId,callback){
    pool.pool.getConnection(function (getConErr, connection) {
        if (getConErr) {
            console.log('YjxActivityModel.js [get202MiniProgramActivityInfo] getConErr' + getConErr);
            return callback(getConErr, null);
        }
        var sql ="	SELECT	" +
                "	  ya.act_id AS id,	" +
                "	  CASE	" +
                "	    WHEN ya.state > 30	" +
                "	    THEN ya.state	" +
                "	    WHEN ya.start_time > SYSDATE()	" +
                "	    THEN 10	" +
                "	    WHEN ya.end_time <= SYSDATE() 	" +
                "	    THEN 30	" +
                "	    ELSE 20	" +
                "	  END AS state,	" +
                "	  ya.theme_img AS thumb,	" +
                "	  ya.url,	" +
                "	  ya.title,	" +
                "	  ya.type,	" +
                "	  ya.images,	" +
                "	  yas.platform_type AS platformType,	" +
                "	  yo.name AS orgName,	" +
                "	  1 AS productLimit,	" +
                "	  yas.reward_total AS praisedReward,	" +
                "	  0 AS praisedRemaining,	" +
                "	  yas.sub_reward_1 AS likeReward,	" +
                "	  0 AS likeRemaining,	" +
                "	  yas.sub_reward_2 AS randomReward , 	" +
                "	  0 AS randomRemaining,	" +
                "	  unix_timestamp(ya.start_time) AS start_time,	" +
                "	  unix_timestamp(ya.end_time) AS end_time,	" +
                "	  ya.desc AS `desc`,	" +
                "	  ya.share_img AS shareImage	" +
                "	FROM	" +
                "	  yjx.yjx_activity ya,	" +
                "	  yjx.yjx_org yo,	" +
                "	  yjx.yjx_act_setting yas	" +
                "	WHERE ya.act_id = "+actId+"	" +
                "	  AND ya.org_id = yo.org_id	" +
                "	  AND ya.as_id = yas.as_id	" +
                "	  AND yo.del_flg = 0	" +
                "	  AND yas.del_flg = 0	" +
                "	  AND ya.del_flg = 0	" ;
            console.log(sql);
        connection.query(sql, function (queryErr, results) {
            connection.release();
            if (queryErr) {
                console.log('YjxActivityModel.js [get202MiniProgramActivityInfo] queryErr' + queryErr)
            }
            return callback(queryErr, results);
        });
    })
};


/**
 * 获取202小程序活动具体规则
 *
 * @author 毛凯
 * @date 2018-7-20
 * @param  actId  活动id
 * @param callback 回调
 *           err: 查询失败
 *           results: 作品列表
 * */
YjxActivityModel.prototype.get202MiniProgramActivityRule= function (actId,callback){
    pool.pool.getConnection(function (getConErr, connection) {
        if (getConErr) {
            console.log('YjxActivityModel.js [get202MiniProgramActivityRule] getConErr' + getConErr);
            return callback(getConErr, null);
        }
        var sql ="	SELECT	" +
                "	yarr.*	" +
                "	FROM	" +
                "	  yjx.yjx_activity ya,	" +
                "	  yjx.yjx_act_setting yas,	" +
                "	  yjx.yjx_act_reward_rule yarr	" +
                "	WHERE ya.act_id = "+actId+"	" +
                "	  AND ya.as_id = yas.as_id	" +
                "	  AND yas.as_id = yarr.as_id	" +
                "	  AND yarr.del_flg = 0	" +
                "	  AND yas.del_flg = 0	" +
                "	  AND ya.del_flg = 0	" ;
            console.log(sql);
        connection.query(sql, function (queryErr, results) {
            connection.release();
            if (queryErr) {
                console.log('YjxActivityModel.js [getActivityCcodeConfigInfo] queryErr' + queryErr)
            }
            return callback(queryErr, results);
        });
    })
};

/**
 * 获取指定活动被点赞列表(每赞一个, 且有金额奖励) sql
 *
 * @author 王俊
 * @date 2018-07-31
 *
 * @param {{Boolen}} beForCnt 是否用于获取个数
 * @param {{Object}} option
 * @param {Array} option.actIds             活动 id 数组
 * @param {Number} option.rewardLimit       奖励金额限制, 大于等于最低限制才算入列表, 单位: 分
 * @param {Number} option.pageNum           页码
 * @param {Number} option.pageSize          页容量
 * @param {Number} [option.orderBy]         排序方式, 1 时间倒序, 默认为 1
 */
function sql_getImagePraiseActivityParticipantList(beForCnt, option) {

    // var sql = '';
    // if(beForCnt) {
    //     sql += ' SELECT COUNT(0) AS count ';
    // } else {
    //     sql += ' SELECT *, ' +
    //         '          ifnull(ytpu.profile_photo, (SELECT yu.profile_photo FROM yjx_user yu WHERE yu.user_id = x.user_id AND yu.del_flg=0) ) AS profilePhoto';
    // }
    // sql += ' FROM ' +
    // '   (SELECT *, ' +
    // '      (SELECT sum(yaur.bal_reward) ' +
    // '       FROM yjx.yjx_act_user_reward yaur ' +
    // '       WHERE yaur.aup_id = yaup.aup_id ' +
    // '         AND yaur.r_type = 32 ' +
    // '         AND yaur.bal_flg = 1 ' +
    // '         AND yaur.del_flg = 0 ' +
    // '         AND yaur.state = 10 ) AS reward ' +
    // '    FROM yjx.yjx_act_user_product yaup ' +
    // '    WHERE yaup.del_flg = 0 ' +
    // '      AND yaup.act_id IN ('+ option.actIds.join(',') +') ) x ' +
    // '       LEFT JOIN yjx.yjx_third_party_user ytpu ON ytpu.user_id = x.user_id AND ytpu.account_type = 202 AND ytpu.del_flg =0 ' +
    // ' WHERE x.reward >= ' + (option.rewardLimit || 0);
    // if(beForCnt) {
    //     sql += ' ORDER BY x.create_time DESC ';
    //     if (typeof option.pageNum !== 'undefined' && option.pageNum !== null &&
    //         typeof option.pageSize !== 'undefined' && option.pageSize !== null)
    //     {
    //         var skipCnt = Number(option.pageNum||'0') * Number(option.pageSize||'0');
    //         sql += " LIMIT " + skipCnt + "," + Number(option.pageSize||'0');
    //     }
    // }
    // return sql

    var sql = '';
    if(beForCnt) {
        sql += ' SELECT COUNT(0) AS count ';
    } else {
        sql += ' SELECT yaur.user_id, ' +
            '        yaur.bal_reward AS reward, ' +
            '        ifnull(ytpu.profile_photo, ' +
            '              ( SELECT yu.profile_photo FROM yjx.yjx_user yu WHERE yu.user_id = yaur.user_id AND yu.del_flg=0 AND yu.user_type = 20))  ' +
            '        AS profilePhoto ';
    }
    sql +=


    ' FROM yjx.yjx_user_like yul, ' +
    '      yjx.yjx_act_user_reward yaur ' +
    ' LEFT JOIN yjx.yjx_third_party_user ytpu ON ytpu.del_flg=0 ' +
    ' AND ytpu.user_id = yaur.user_id ' +
    ' AND ytpu.account_type = 202 ' +
    ' WHERE yaur.del_flg=0 ' +
    '   AND yaur.state = 10 ' +
    '   AND yaur.bal_flg = 1 ' +
    '   AND yaur.r_type = 32 ' +
    '   AND yaur.act_id IN ('+option.actIds.join(',')+') ' +
    '   AND yul.del_flg=0 ' +
    '   AND yul.like_id = yaur.like_id ' +
    '   AND yul.type = 10 ';
    if(!beForCnt) {
        sql += ' ORDER BY yul.create_time DESC ';
        if (typeof option.pageNum !== 'undefined' && option.pageNum !== null &&
            typeof option.pageSize !== 'undefined' && option.pageSize !== null)
        {
            var skipCnt = Number(option.pageNum||'0') * Number(option.pageSize||'0');
            sql += " LIMIT " + skipCnt + "," + Number(option.pageSize||'0');
        }
    }
    return sql;
}

/**
 * 获取指定活动被点赞列表(每赞一个, 且有金额奖励)
 *
 * @author 王俊
 * @date 2018-07-31
 *
 * @param {{Object}} option
 * @param {Array} option.actIds             活动 id 数组
 * @param {Number} option.rewardLimit       奖励金额限制, 大于等于最低限制才算入列表, 单位: 分
 * @param {Number} option.pageNum           页码
 * @param {Number} option.pageSize          页容量
 * @param {Number} [option.orderBy]         排序方式, 1 时间倒序, 默认为 1
 * @param {Function} callback
 *                      error:
 *                      results: []
 *
 */
YjxActivityModel.prototype.getImagePraiseActivityParticipantList = function(option, callback) {
    pool.pool.getConnection(function (getConErr, connection) {
        if (getConErr) {
            console.log('YjxActivityModel.js [getImagePraiseActivityParticipantList] getConErr' + getConErr);
            return callback(getConErr, null);
        }
        var sql = sql_getImagePraiseActivityParticipantList(false, option);
        console.log(sql);
        connection.query(sql, function (queryErr, results) {
            connection.release();
            if (queryErr) {
                console.log('YjxActivityModel.js [getImagePraiseActivityParticipantList] queryErr' + queryErr)
            }
            return callback(queryErr, results);
        });
    })
};

/**
 * 获取指定活动被点赞个数(每赞一个, 且有金额奖励)
 *
 * @author 王俊
 * @date 2018-07-31
 *
 * @param {{Object}} option
 * @param {Array} option.actIds             活动 id 数组
 * @param {Number} option.rewardLimit       奖励金额限制, 大于等于最低限制才算入列表, 单位: 分
 * @param {Number} [option.orderBy]         排序方式, 1 时间倒序, 默认为 1
 * @param {Function} callback
 *                      error:
 *                      count:
 *
 */
YjxActivityModel.prototype.getImagePraiseActivityParticipantListCnt = function(option, callback) {
    pool.pool.getConnection(function (getConErr, connection) {
        if (getConErr) {
            console.log('YjxActivityModel.js [getImagePraiseActivityParticipantListCnt] getConErr' + getConErr);
            return callback(getConErr, null);
        }
        var sql = sql_getImagePraiseActivityParticipantList(true, option);
        console.log(sql);
        connection.query(sql, function (queryErr, results) {
            connection.release();
            if (queryErr || !results || results.length <=0) {
                console.log('YjxActivityModel.js [getImagePraiseActivityParticipantListCnt] queryErr' + queryErr);
                return callback(queryErr || '获取活动参与者列表出错', 0);
            }
            return callback(queryErr, results[0].count);
        });
    })
};

/**
 * 查询需要自动确认的活动列表
 *
 * @author 王俊
 * @date 2018-08-02
 *
 * @param {Object} option
 * @param {Function} callback
 *                      err:
 */
YjxActivityModel.prototype.selectNeedAutoCheckActivityList = function(option, callback) {
    pool.pool.getConnection(function (getConErr, connection) {
        if (getConErr) {
            console.log('YjxActivityModel.js [selectNeedAutoCheckActivityList] getConErr' + getConErr);
            return callback(getConErr, null);
        }
        var sql = ' SELECT *' +
            ' FROM yjx.yjx_activity ya' +
            ' WHERE ya.del_flg = 0' +
            '   AND ya.type = 20' +
            '   AND ya.publish = 1' +
            '   AND (ya.state = 10 OR ya.state = 20 || ya.state = 30)';
        console.log(sql);
        connection.query(sql, function (queryErr, results) {
            connection.release();
            if (queryErr || !results || results.length <=0) {
                console.log('YjxActivityModel.js [selectNeedAutoCheckActivityList] queryErr' + queryErr);
            }
            return callback(queryErr, results);
        });
    })
};



module.exports = YjxActivityModel;

